//package com.yunti.springboot.util;
//
//import com.spire.xls.*;
//import com.yunti.cloudsportmiddle.model.commonEnum.SubjectEnum;
//import com.yunti.cloudsportmiddle.po.Examdetail;
//import com.yunti.cloudsportmiddle.po.Score;
//import com.yunti.cloudsportmiddle.po.Student;
//import com.yunti.cloudsportmiddle.vo.res.TransScore;
//import com.yunti.springboot.po.ElectiveSub;
//import com.yunti.springboot.po.Score;
//import com.yunti.springboot.po.Student;
//import lombok.AllArgsConstructor;
//import lombok.Data;
//import lombok.NoArgsConstructor;
//import org.springframework.core.io.ClassPathResource;
//
//import javax.servlet.http.HttpServletResponse;
//import java.awt.*;
//import java.io.IOException;
//import java.io.InputStream;
//import java.math.RoundingMode;
//import java.net.URLEncoder;
//import java.text.DecimalFormat;
//import java.text.NumberFormat;
//import java.util.ArrayList;
//import java.util.Comparator;
//import java.util.List;
//import java.util.Map;
//import java.util.concurrent.atomic.AtomicBoolean;
//import java.util.concurrent.atomic.AtomicReference;
//import java.util.function.Function;
//import java.util.stream.Collectors;
//
///**
// * @author yaming
// */
//public class ScoreExcelUtils {
//
//    public static void main(String[] args) throws IOException {
////        buildTemplate();
////        change();
//        create();
//    }
//
//    private static void create() throws IOException {
//        //加载Excel文档
//        Workbook eightWorkbook = new Workbook();
//        ClassPathResource classPathResource = new ClassPathResource("/ftl/2020年级8学年期成绩.xlsx");
//        InputStream inputStream = classPathResource.getInputStream();
//        eightWorkbook.loadFromStream(inputStream, ExcelVersion.Version2013);
//
//        //获取第2张工作表
//        Worksheet sheet1 = eightWorkbook.getWorksheets().get(0);
//
//
//        CellRange[] cellRanges = sheet1.getRows();
//        List<SgandTzScore> sgandTzScores = new ArrayList<>();
//        for (int i = 1; i < cellRanges.length; i++) {
//            CellRange[] cellRanges2 = cellRanges[i].getColumns();
//
//            try {
//                SgandTzScore sgandTzScore = new SgandTzScore(cellRanges2[0].getText(), cellRanges2[1].getText(), cellRanges2[2].getText(), cellRanges2[3].getText(), Double.parseDouble(cellRanges2[4].getValue()), Double.parseDouble(cellRanges2[5].getValue()));
//                System.out.println(sgandTzScore);
//                sgandTzScores.add(sgandTzScore);
//            } catch (Exception e) {
//
//            }
//
//        }
//
//        Map<String,SgandTzScore> sgandTzScoreHashMap =  sgandTzScores.stream().collect(Collectors.toMap(SgandTzScore::getStuNo, Function.identity()));
//
//
//        //加载Excel文档
//        Workbook nineWorkbook = new Workbook();
//        ClassPathResource classPathResource2 = new ClassPathResource("/ftl/2020年级9学年成绩.xlsx");
//        InputStream inputStream2 = classPathResource2.getInputStream();
//        nineWorkbook.loadFromStream(inputStream2, ExcelVersion.Version2013);
//        //获取第2张工作表
//        Worksheet sheet2 = nineWorkbook.getWorksheets().get(0);
//
//        CellRange[] cellRanges2 = sheet2.getRows();
//
//        int fhl = 0;
//        int tz = 0;
//        for (int i = 1; i < cellRanges2.length; i++) {
//            CellRange[] cellRanges3 = cellRanges2[i].getColumns();
//            //肺活量体重指数0分
//            try{
//                if(Double.parseDouble(cellRanges3[31].getValue()) == 0){
//                    System.out.print(";"+cellRanges3[3].getText() + ":" + cellRanges3[2].getValue());
//                    System.out.print(";原肺活量体重指数:"+ sgandTzScoreHashMap.get(cellRanges3[2].getText().trim()).getFhlZs());
//                    System.out.print(";新肺活量体重指数:"+cellRanges3[30].getValue());
//
//                    if(sgandTzScoreHashMap.get(cellRanges3[2].getText()).getFhlZs() <= Double.parseDouble(cellRanges3[30].getValue())){
//                        System.out.println("-更好");
//                        cellRanges3[31].setValue(String.valueOf(1.5));
//                        fhl++;
//                    }
//                    else System.out.println();
//                }
//            }catch (Throwable e){
//            }
//
//            //BMI指数0分
//            try{
//                if(Double.parseDouble(cellRanges3[27].getValue()) == 0){
//                    System.out.print(";"+cellRanges3[3].getText() + ":" + cellRanges3[2].getValue());
//                    System.out.print(";原身高体重指数:"+ sgandTzScoreHashMap.get(cellRanges3[2].getText().trim()).getBmi());
//                    System.out.print(";新身高体重指数:"+cellRanges3[26].getValue());
//
//                    if(sgandTzScoreHashMap.get(cellRanges3[2].getText()).getBmi() >= Double.parseDouble(cellRanges3[26].getValue())){
//                        System.out.println("-更好");
//                        cellRanges3[27].setValue(String.valueOf(1.5));
//                        tz++;
//                    }
//                    else System.out.println();
//                }
//            }catch (Exception e){
//
//            }
//        }
//
//        //保存文档
//        nineWorkbook.saveToFile("output2.xlsx", ExcelVersion.Version2013);
//        System.out.println(fhl);
//        System.out.println(tz);
//    }
//
//
//    public static void change() throws IOException {
//        //加载Excel文档
//        Workbook workbook = new Workbook();
//
//        //创建数据单元格样式
//        CellStyle style2 = workbook.getStyles().addStyle("Data Style");
//        style2.getFont().setSize(10f);
//        style2.getFont().setColor(Color.BLACK);
//
//
//        ClassPathResource classPathResource = new ClassPathResource("/ftl/nineScoreExcelTemplate.xlsx");
//        InputStream inputStream = classPathResource.getInputStream();
//
//        workbook.loadFromStream(inputStream, ExcelVersion.Version2013);
//
//        //获取第2张工作表
//        Worksheet sheet = workbook.getWorksheets().get(0);
//
//        CellRange cell = sheet.getCellRange(7, 1);
//        cell.setValue("Data " + 1 + ", " + 7);
//        cell.setStyle(style2);
//
//
//        //保存文档
//        workbook.saveToFile("output.xlsx", ExcelVersion.Version2013);
//
//    }
//
//
//    public static void buildTemplate() {
////创建Workbook实例
//        Workbook workbook = new Workbook();
//
//        //获取第一张工作表（新建的Workbook默认包含3张工作表）
//        Worksheet sheet = workbook.getWorksheets().get(0);
//        //为第一张工作表设置名称
//        sheet.setName("九年级");
//
//        //冻结首行
//        sheet.freezePanes(1, 7);
//
//
//        //创建列头单元格样式
//        CellStyle style1 = workbook.getStyles().addStyle("Header Style");
//        style1.getFont().setSize(20f);
//        style1.getFont().setColor(Color.BLACK);
//        style1.getFont().isBold(true);
//        style1.setHorizontalAlignment(HorizontalAlignType.Center);
//        style1.setVerticalAlignment(VerticalAlignType.Center);
//
//        //创建数据单元格样式
//        CellStyle style2 = workbook.getStyles().addStyle("Data Style");
//        style2.getFont().setSize(10f);
//        style2.getFont().setColor(Color.BLACK);
//
//        CellRange header = sheet.getCellRange(1, 1);
//        header.setValue("昆明市呈贡区第一中学");
//        header.setStyle(style1);
//        header.setColumnWidth(15f);
//
//        CellRange header2 = sheet.getCellRange(1, 5);
//        header2.setValue("初中学生体育科目测试成绩登记表");
//        header2.setStyle(style1);
//        header2.setColumnWidth(15f);
//
//        //按范围合并单元格
//        sheet.getRange().get("A2:A5").merge();
//        sheet.getRange().get("B2:B5").merge();
//        sheet.getRange().get("C2:C5").merge();
//        sheet.getRange().get("D2:D5").merge();
//        sheet.getRange().get("E2:E5").merge();
//        sheet.getRange().get("F2:F5").merge();
//
//
//        CellRange cellRange = sheet.getCellRange(2, 1);
//        cellRange.setValue("序号");
//        cellRange.setStyle(style2);
//        cellRange.setColumnWidth(15f);
//
//        cellRange = sheet.getCellRange(2, 2);
//        cellRange.setValue("班级");
//        cellRange.setStyle(style2);
//        cellRange.setColumnWidth(15f);
//
//        cellRange = sheet.getCellRange(2, 3);
//        cellRange.setValue("身份证号");
//        cellRange.setStyle(style2);
//        cellRange.setColumnWidth(15f);
//
//        cellRange = sheet.getCellRange(2, 4);
//        cellRange.setValue("学籍号");
//        cellRange.setStyle(style2);
//        cellRange.setColumnWidth(15f);
//
//        cellRange = sheet.getCellRange(2, 5);
//        cellRange.setValue("姓名");
//        cellRange.setStyle(style2);
//        cellRange.setColumnWidth(15f);
//
//        cellRange = sheet.getCellRange(2, 6);
//        cellRange.setValue("性别");
//        cellRange.setStyle(style2);
//        cellRange.setColumnWidth(15f);
//
//
//        //将合并单元格的水平对齐方式设置为居中
//        sheet.getRange().get("A2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
//        sheet.getRange().get("B2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
//        sheet.getRange().get("C2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
//        sheet.getRange().get("D2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
//        sheet.getRange().get("E2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
//        sheet.getRange().get("F2").getCellStyle().setHorizontalAlignment(HorizontalAlignType.Center);
//
//        //将合并单元格的垂直对齐方式设置为居中
//        sheet.getRange().get("A2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
//        sheet.getRange().get("B2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
//        sheet.getRange().get("C2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
//        sheet.getRange().get("D2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
//        sheet.getRange().get("E2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
//        sheet.getRange().get("F2").getCellStyle().setVerticalAlignment(VerticalAlignType.Center);
//
//        //保存结果文件
//        workbook.saveToFile("CreateExcel.xlsx", FileFormat.Version2013);
//    }
//
//    static DecimalFormat df = new DecimalFormat("0.00");
//
//    public static void outputSevenScore(List<Student> students, List<ElectiveSub> electiveSubs, List<Score> scoreList, HttpServletResponse response) {
//        //加载Excel文档
//        Workbook workbook = new Workbook();
//
//        //创建数据单元格样式
//        CellStyle style2 = workbook.getStyles().addStyle("Data Style");
//        style2.getFont().setSize(10f);
//        style2.getFont().setColor(Color.BLACK);
//        ClassPathResource classPathResource = new ClassPathResource("/ftl/sevenScoreExcelTemplate.xlsx");
//        InputStream inputStream = null;
//        try {
//            inputStream = classPathResource.getInputStream();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//        workbook.loadFromStream(inputStream, ExcelVersion.Version2013);
//        Map<Integer, List<Score>> scoreListMap = scoreList.stream().collect(Collectors.groupingBy(Score::getStuId));
//
//        //获取第2张工作表
//        Worksheet sheet = workbook.getWorksheets().get(0);
//        for (ElectiveSub electiveSub : electiveSubs) {
//                for (int i = 0; i < students.size(); i++) {
//                    Student student = students.get(i);
//                    CellRange cell = sheet.getCellRange(6 + i, 1);
//                    cell.setValue(student.getClassName());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(6 + i, 2);
//                    cell.setValue("'" + student.getStuNo());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(6 + i, 3);
//                    cell.setValue("'" + student.getStuNo());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(6 + i, 4);
//                    cell.setValue(student.getName());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(6 + i, 5);
//                    cell.setValue(student.getSex() == 1 ? "男" : "女");
//                    cell.setStyle(style2);
//
//                    //学生成绩列表
//                    List<Score> stuScoreList = scoreListMap.get(student.getId());
//                    int upXkCount = 1;
//                    int dowmXkCount = 1;
//
//                    List<Score> sgExamdetail = null;
//                    List<Score> tzExamdetail = null;
//                    List<Score> fhlExamdetail = null;
//
//                    for (int j = 0; j < stuScoreList.size(); j++) {
////                        List<Score> scoreList2 = scoreListMap.get(electiveSub.getId());
////                        if (scoreList2 == null) continue;
//
//
//
//
//                        TransScore transScore = getScore(electiveSub, scoreList2);
//                        if (electiveSub.getSemester() == 1 && electiveSub.getSubId() == 1) {
//                            cell = sheet.getCellRange(6 + i, 6);
//                            cell.setValue("'" + transScore.getRawScore() + "s");
//                            cell.setStyle(style2);
//
//                            cell = sheet.getCellRange(6 + i, 7);
//                            cell.setValue(transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//
//                        if (electiveSub.getSemester() == 1 && electiveSub.getSubId() == 2) {
//                            cell = sheet.getCellRange(6 + i, 8);
//                            cell.setValue(transScore.getRawScore() + "个");
//                            cell.setStyle(style2);
//
//                            cell = sheet.getCellRange(6 + i, 9);
//                            cell.setValue(transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//
//                        if (electiveSub.getSemester() == 1 && electiveSub.getSubId() == 3) {
//                            cell = sheet.getCellRange(6 + i, 10);
//                            cell.setValue("'" + transScore.getRawScore());
//                            cell.setStyle(style2);
//
//                            cell = sheet.getCellRange(6 + i, 11);
//                            cell.setValue(transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//
//                        if (electiveSub.getSemester() == 1 && electiveSub.getSubId() == 4) {
//                            cell = sheet.getCellRange(6 + i, 10);
//                            cell.setValue("'" + transScore.getRawScore());
//                            cell.setStyle(style2);
//
//                            cell = sheet.getCellRange(6 + i, 11);
//                            cell.setValue(transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//
//                        //6选2
//                        if (electiveSub.getSemester() == 2 && (electiveSub.getSubId() >= 5 && electiveSub.getSubId() <= 11)) {
//                            if (upXkCount == 1) {
//                                cell = sheet.getCellRange(6 + i, 25);
//                                cell.setValue(SubjectEnum.getNameById(electiveSub.getSubId()));
//                                System.out.println(SubjectEnum.getNameById(electiveSub.getSubId()));
//                                cell.setStyle(style2);
//
//                                cell = sheet.getCellRange(6 + i, 26);
//                                cell.setValue(transScore.getRawScore());
//                                cell.setStyle(style2);
//
//                                cell = sheet.getCellRange(6 + i, 27);
//                                cell.setValue(transScore.getConvScore());
//                                cell.setStyle(style2);
//
//                            }
//
//                            if (upXkCount == 2) {
//                                cell = sheet.getCellRange(6 + i, 28);
//                                cell.setValue(SubjectEnum.getNameById(electiveSub.getSubId()));
//                                System.out.println("2:"+SubjectEnum.getNameById(electiveSub.getSubId()));
//                                cell.setStyle(style2);
//
//                                cell = sheet.getCellRange(6 + i, 29);
//                                cell.setValue(transScore.getRawScore());
//                                cell.setStyle(style2);
//
//                                cell = sheet.getCellRange(6 + i, 30);
//                                cell.setValue(transScore.getConvScore());
//                                cell.setStyle(style2);
//                            }
//                            upXkCount++;
//                        }
//
//
//                        if (electiveSub.getSemester() == 2 && electiveSub.getSubId() == 12) {
//                            cell = sheet.getCellRange(6 + i, 19);
//                            cell.setValue("'" + transScore.getRawScore() + "s");
//                            cell.setStyle(style2);
//
//                            cell = sheet.getCellRange(6 + i, 20);
//                            cell.setValue(transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//
//                        if (electiveSub.getSemester() == 2 && electiveSub.getSubId() == 2) {
//                            cell = sheet.getCellRange(6 + i, 21);
//                            cell.setValue("'" + transScore.getRawScore() + "个");
//                            cell.setStyle(style2);
//
//                            cell = sheet.getCellRange(6 + i, 22);
//                            cell.setValue(transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//
//                        if (electiveSub.getSemester() == 2 && electiveSub.getSubId() == 3) {
//                            cell = sheet.getCellRange(6 + i, 23);
//                            cell.setValue("'" + transScore.getRawScore());
//                            cell.setStyle(style2);
//
//                            cell = sheet.getCellRange(6 + i, 24);
//                            cell.setValue(transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//
//                        if (electiveSub.getSemester() == 6 && electiveSub.getSubId() == 4) {
//                            cell = sheet.getCellRange(6 + i, 23);
//                            cell.setValue("'" + transScore.getRawScore());
//                            cell.setStyle(style2);
//
//                            cell = sheet.getCellRange(6 + i, 24);
//                            cell.setValue(transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//
//                        if (electiveSub.getSubId() == 26) {
//                            sgExamdetail = electiveSub;
//                        }
//                        if (electiveSub.getSubId() == 13) {
//                            tzExamdetail = electiveSub;
//                        }
//                        if (electiveSub.getSubId() == 14) {
//                            fhlExamdetail = electiveSub;
//                        }
//
//                        if (electiveSub.getSubId() == 15 || electiveSub.getSubId() == 16 || electiveSub.getSubId() == 17) {
//                            SubjectEnum subject = SubjectEnum.getEnumBySubId(electiveSub.getSubId());
//                            cell = sheet.getCellRange(6 + i, 31);
//                            cell.setValue("'" + subject.getMessage());
//                            cell.setStyle(style2);
//                            if (transScore.getTransScoreItemList() == null) {
//                                continue;
//                            }
//
//                            for (int k = 0; k < transScore.getTransScoreItemList().size(); k++) {
//                                TransScore.TransScoreItem transScoreItem = transScore.getTransScoreItemList().get(k);
//                                cell = sheet.getCellRange(6 + i, 32 + k);
//                                if (electiveSub.getSubId() == 17 && k == 0) {
//                                    cell.setValue("行进间正面双手连续传垫球 " + transScoreItem.getRawScore() + " - " + transScoreItem.getConvScore());
//                                }
//                                if (electiveSub.getSubId() == 17 && k == 1) {
//                                    cell.setValue("正面上手发球（男）/正面下手发球（女）" + transScoreItem.getRawScore() + "-" + transScoreItem.getConvScore());
//                                }
//                                cell.setStyle(style2);
//                            }
//
//                            cell = sheet.getCellRange(6 + i, 35);
//                            cell.setValue("'" + transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//
//                        if (electiveSub.getSubId() >= 18 && electiveSub.getSubId() <= 22) {
//                            SubjectEnum subject = SubjectEnum.getEnumBySubId(electiveSub.getSubId());
//                            cell = sheet.getCellRange(6 + i, 36);
//                            cell.setValue("'" + subject.getMessage());
//                            cell.setStyle(style2);
//
//                            for (int k = 0; k < transScore.getTransScoreItemList().size(); k++) {
//                                TransScore.TransScoreItem transScoreItem = transScore.getTransScoreItemList().get(k);
//                                cell = sheet.getCellRange(6 + i, 37 + k);
//                                if (electiveSub.getSubId() == 22 && k == 0) {
//                                    cell.setValue("10秒钟俯卧撑+左、右直拳+深蹲+左、右鞭腿 - " + transScoreItem.getRawScore() + " -组 " + transScoreItem.getConvScore());
//                                }
//                                if (electiveSub.getSubId() == 22 && k == 1) {
//                                    cell.setValue("10秒钟左、右正蹬腿 - " + transScoreItem.getRawScore() + " -组 " + transScoreItem.getConvScore());
//                                }
//                                cell.setStyle(style2);
//                            }
//
//                            cell = sheet.getCellRange(6 + i, 41);
//                            cell.setValue("'" + transScore.getConvScore());
//                            cell.setStyle(style2);
//                        }
//                    }
//
//                    if (sgExamdetail != null && tzExamdetail != null) {
//                        List<Score> scoreList2 = scoreListMap.get(sgExamdetail.getId());
//                        TransScore sgTransScore = getScore(sgExamdetail, scoreList2);
//
//                        List<Score> scoreList3 = scoreListMap.get(tzExamdetail.getId());
//                        TransScore tzTransScore = getScore(tzExamdetail, scoreList3);
//
//                        cell = sheet.getCellRange(6 + i, 12);
//                        cell.setValue(sgTransScore.getRawScore());
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(6 + i, 13);
//                        cell.setValue(tzTransScore.getRawScore());
//                        cell.setStyle(style2);
//
//                        Double bmi = Double.parseDouble(tzTransScore.getRawScore()) / ((Double.parseDouble(sgTransScore.getRawScore()) / 100.0) * (Double.parseDouble(sgTransScore.getRawScore()) / 100.0));
//
//                        cell = sheet.getCellRange(6 + i, 14);
//                        cell.setValue("'" + df.format(bmi));
//                        cell.setStyle(style2);
//
//                    }
//
//                    if (fhlExamdetail != null && tzExamdetail != null) {
//                        List<Score> scoreList2 = scoreListMap.get(fhlExamdetail.getId());
//                        TransScore fhlTransScore = getScore(fhlExamdetail, scoreList2);
//
//                        List<Score> scoreList3 = scoreListMap.get(tzExamdetail.getId());
//                        TransScore tzTransScore = getScore(tzExamdetail, scoreList3);
//
//                        cell = sheet.getCellRange(6 + i, 15);
//                        cell.setValue(fhlTransScore.getRawScore());
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(6 + i, 16);
//                        cell.setValue(tzTransScore.getRawScore());
//                        cell.setStyle(style2);
//
//
//                        cell = sheet.getCellRange(6 + i, 17);
//                        cell.setValue("'" + df.format(Double.parseDouble(fhlTransScore.getRawScore()) / Double.parseDouble(tzTransScore.getRawScore())));
//
//                        cell.setStyle(style2);
//                    }
//
//                    cell = sheet.getCellRange(6 + i, 18);
//                    cell.setFormula("=SUM(Q" + (6 + i) +"+N" + (6 + i) +"+K" + (6 + i) +" +I " + (6 + i) +" + G" + (6 + i) +")");
//                    cell.setStyle(style2);
//                }
//
//            }
//
//        //获取单元格，设置内边框、外边框
//        CellRange range1 = sheet.getCellRange("A1:AZ500");
//        range1.borderInside(LineStyleType.Thin, Color.BLACK);
//        range1.borderAround(LineStyleType.Medium, Color.BLACK);
//
//
//        //保存文档
//        workbook.saveToFile("output.xlsx", ExcelVersion.Version2013);
//        try {
//            // 设置输出的格式
//            response.setContentType("application/octet-stream");
//            response.setCharacterEncoding("UTF-8");
//            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
//            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("fileName","utf-8"));
//            workbook.saveToStream(response.getOutputStream(), FileFormat.Version2013);
//        } catch (IOException  e) {
//            e.printStackTrace();
//        }
//    }
//
//
//    public static void outputEightScore(List<Student> students, List<Examdetail> examdetailList, List<Score> scoreList, HttpServletResponse response) {
//        //加载Excel文档
//        Workbook workbook = new Workbook();
//
//        //创建数据单元格样式
//        CellStyle style2 = workbook.getStyles().addStyle("Data Style");
//        style2.getFont().setSize(10f);
//        style2.getFont().setColor(Color.BLACK);
//
//
//        ClassPathResource classPathResource = new ClassPathResource("/ftl/eightScoreExcelTemplate.xlsx");
//        InputStream inputStream = null;
//        try {
//            inputStream = classPathResource.getInputStream();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//
//        workbook.loadFromStream(inputStream, ExcelVersion.Version2013);
//
//        //获取第2张工作表
//        Worksheet sheet = workbook.getWorksheets().get(0);
//
//        Map<Integer, List<Examdetail>> examdetailListMap = examdetailList.stream().collect(Collectors.groupingBy(Examdetail::getStuId));
//        Map<Integer, List<Score>> scoreListMap = scoreList.stream().collect(Collectors.groupingBy(Score::getExamdetailId));
//
//        for (int i = 0; i < students.size(); i++) {
//            Student student = students.get(i);
//            CellRange cell = sheet.getCellRange(7 + i, 1);
//            cell.setValue(student.getClassName());
//            cell.setStyle(style2);
//
//            cell = sheet.getCellRange(7 + i, 2);
//            cell.setValue("'" + student.getStuNo());
//            cell.setStyle(style2);
//
//            cell = sheet.getCellRange(7 + i, 3);
//            cell.setValue("'" + student.getStuNo());
//            cell.setStyle(style2);
//
//            cell = sheet.getCellRange(7 + i, 4);
//            cell.setValue(student.getName());
//            cell.setStyle(style2);
//
//            cell = sheet.getCellRange(7 + i, 5);
//            cell.setValue(student.getSex() == 1 ? "男" : "女");
//            cell.setStyle(style2);
//
//
//            List<Examdetail> examdetails = examdetailListMap.get(student.getId());
//            int upXkCount = 1;
//            int dowmXkCount = 1;
//
//            Examdetail sgExamdetail = null;
//            Examdetail tzExamdetail = null;
//            Examdetail fhlExamdetail = null;
//
//
//            for (int j = 0; j < examdetails.size(); j++) {
//                Examdetail examdetail = examdetails.get(j);
//                List<Score> scoreList2 = scoreListMap.get(examdetail.getId());
//                if (scoreList2 == null) continue;
//                TransScore transScore = getScore(examdetail, scoreList2);
//                if (examdetail.getSemester() == 3 && examdetail.getSubId() == 1) {
//                    cell = sheet.getCellRange(7 + i, 6);
//                    cell.setValue("'" + transScore.getRawScore() + "s");
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 7);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 3 && examdetail.getSubId() == 2) {
//                    cell = sheet.getCellRange(7 + i, 8);
//                    cell.setValue(transScore.getRawScore() + "个");
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 9);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 3 && examdetail.getSubId() == 3) {
//                    cell = sheet.getCellRange(7 + i, 10);
//                    cell.setValue("'" + transScore.getRawScore());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 11);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 3 && examdetail.getSubId() == 4) {
//                    cell = sheet.getCellRange(7 + i, 10);
//                    cell.setValue("'" + transScore.getRawScore());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 11);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                //6选2
//                if (examdetail.getSemester() == 3 && (examdetail.getSubId() >= 5 && examdetail.getSubId() <= 11)) {
//                    if (upXkCount == 1) {
//                        cell = sheet.getCellRange(7 + i, 12);
//                        cell.setValue(SubjectEnum.getNameById(examdetail.getSubId()));
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(7 + i, 13);
//                        cell.setValue(transScore.getRawScore());
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(7 + i, 14);
//                        cell.setValue(transScore.getConvScore());
//                        cell.setStyle(style2);
//                        upXkCount++;
//                    }
//
//                    if (upXkCount == 2) {
//                        cell = sheet.getCellRange(7 + i, 15);
//                        cell.setValue(SubjectEnum.getNameById(examdetail.getSubId()));
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(7 + i, 16);
//                        cell.setValue(transScore.getRawScore());
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(7 + i, 17);
//                        cell.setValue(transScore.getConvScore());
//                        cell.setStyle(style2);
//                    }
//                }
//
//
//                if (examdetail.getSemester() == 4 && examdetail.getSubId() == 12) {
//                    cell = sheet.getCellRange(7 + i, 19);
//                    cell.setValue("'" + transScore.getRawScore() + "s");
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 20);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 4 && examdetail.getSubId() == 2) {
//                    cell = sheet.getCellRange(7 + i, 21);
//                    cell.setValue("'" + transScore.getRawScore() + "个");
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 22);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 4 && examdetail.getSubId() == 3) {
//                    cell = sheet.getCellRange(7 + i, 23);
//                    cell.setValue("'" + transScore.getRawScore());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 24);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 4 && examdetail.getSubId() == 4) {
//                    cell = sheet.getCellRange(7 + i, 23);
//                    cell.setValue("'" + transScore.getRawScore());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 24);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSubId() == 26) {
//                    sgExamdetail = examdetail;
//                }
//                if (examdetail.getSubId() == 13) {
//                    tzExamdetail = examdetail;
//                }
//                if (examdetail.getSubId() == 14) {
//                    fhlExamdetail = examdetail;
//                }
//
//                if (examdetail.getSubId() == 15 || examdetail.getSubId() == 16 || examdetail.getSubId() == 17) {
//                    SubjectEnum subject = SubjectEnum.getEnumBySubId(examdetail.getSubId());
//                    cell = sheet.getCellRange(7 + i, 33);
//                    cell.setValue("'" + subject.getMessage());
//                    cell.setStyle(style2);
//                    if (transScore.getTransScoreItemList() == null) {
//                        continue;
//                    }
//
//                    for (int k = 0; k < transScore.getTransScoreItemList().size(); k++) {
//                        TransScore.TransScoreItem transScoreItem = transScore.getTransScoreItemList().get(k);
//                        cell = sheet.getCellRange(7 + i, 34 + k);
//                        if (examdetail.getSubId() == 17 && k == 0) {
//                            cell.setValue("自传隔网向前传球 " + transScoreItem.getRawScore() + " - " + transScoreItem.getConvScore());
//                        }
//                        if (examdetail.getSubId() == 17 && k == 1) {
//                            cell.setValue("自垫隔网向前垫球" + transScoreItem.getRawScore() + "-" + transScoreItem.getConvScore());
//                        }
//                        if (examdetail.getSubId() == 17 && k == 2) {
//                            cell.setValue("正面下手发球" + transScoreItem.getRawScore() + "-" + transScoreItem.getConvScore());
//                        }
//                        cell.setStyle(style2);
//                    }
//
//                    cell = sheet.getCellRange(7 + i, 37);
//                    cell.setValue("'" + transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSubId() >= 18 && examdetail.getSubId() <= 22) {
//                    SubjectEnum subject = SubjectEnum.getEnumBySubId(examdetail.getSubId());
//                    cell = sheet.getCellRange(7 + i, 38);
//                    cell.setValue("'" + subject.getMessage());
//                    cell.setStyle(style2);
//
//                    for (int k = 0; k < transScore.getTransScoreItemList().size(); k++) {
//                        TransScore.TransScoreItem transScoreItem = transScore.getTransScoreItemList().get(k);
//                        cell = sheet.getCellRange(7 + i, 39 + k);
//                        if (examdetail.getSubId() == 22 && k == 0) {
//                            cell.setValue("10秒钟左右直拳+左右鞭腿-" + transScoreItem.getRawScore() + " -组 " + transScoreItem.getConvScore());
//                        }
//                        if (examdetail.getSubId() == 22 && k == 1) {
//                            cell.setValue("10秒钟俯卧撑+左右直拳+左右鞭腿 - " + transScoreItem.getRawScore() + " -组 " + transScoreItem.getConvScore());
//                        }
//                        cell.setStyle(style2);
//                    }
//
//                    cell = sheet.getCellRange(7 + i, 44);
//                    cell.setValue("'" + transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//            }
//            if (sgExamdetail != null && tzExamdetail != null) {
//                List<Score> scoreList2 = scoreListMap.get(sgExamdetail.getId());
//                TransScore sgTransScore = getScore(sgExamdetail, scoreList2);
//
//                List<Score> scoreList3 = scoreListMap.get(tzExamdetail.getId());
//                TransScore tzTransScore = getScore(tzExamdetail, scoreList3);
//
//                cell = sheet.getCellRange(7 + i, 25);
//                cell.setValue(sgTransScore.getRawScore());
//                cell.setStyle(style2);
//
//                cell = sheet.getCellRange(7 + i, 26);
//                cell.setValue(tzTransScore.getRawScore());
//                cell.setStyle(style2);
//
//                Double bmi = Double.parseDouble(tzTransScore.getRawScore()) / ((Double.parseDouble(sgTransScore.getRawScore()) / 100.0) * (Double.parseDouble(sgTransScore.getRawScore()) / 100.0));
//                double convScore = 0;
//                if ((student.getSex() == 1 && bmi <= 26) || (student.getSex() == 2 && bmi <= 25.1)) {
//                    convScore = 1.5;
//                }
//
//                cell = sheet.getCellRange(7 + i, 27);
//                cell.setValue("'" + df.format(bmi));
//                cell.setStyle(style2);
//
//                cell = sheet.getCellRange(7 + i, 28);
//                cell.setValue(convScore + "");
//                cell.setStyle(style2);
//
//            }
//
//            if (fhlExamdetail != null && tzExamdetail != null) {
//                List<Score> scoreList2 = scoreListMap.get(fhlExamdetail.getId());
//                TransScore fhlTransScore = getScore(fhlExamdetail, scoreList2);
//
//                List<Score> scoreList3 = scoreListMap.get(tzExamdetail.getId());
//                TransScore tzTransScore = getScore(tzExamdetail, scoreList3);
//
//                cell = sheet.getCellRange(7 + i, 29);
//                cell.setValue(fhlTransScore.getRawScore());
//                cell.setStyle(style2);
//
//                cell = sheet.getCellRange(7 + i, 30);
//                cell.setValue(tzTransScore.getRawScore());
//                cell.setStyle(style2);
//
//
//                cell = sheet.getCellRange(7 + i, 31);
//                cell.setValue("'" + df.format(Double.parseDouble(fhlTransScore.getRawScore()) / Double.parseDouble(tzTransScore.getRawScore())));
//
//                cell.setStyle(style2);
//
//                double convScore = 0;
//                if ((student.getSex() == 1 && Double.parseDouble(fhlTransScore.getRawScore()) / Double.parseDouble(tzTransScore.getRawScore()) <= 50) || (student.getSex() == 2 && Double.parseDouble(fhlTransScore.getRawScore()) / Double.parseDouble(tzTransScore.getRawScore()) <= 60)) {
//                    convScore = 1.5;
//                }
//
//                cell = sheet.getCellRange(7 + i, 32);
//                cell.setValue(convScore + "");
//                cell.setStyle(style2);
//
//            }
//
//            cell = sheet.getCellRange(7 + i, 18);
//            cell.setStyle(style2);
//            cell.setFormula( String.format("=SUM(Q%d+N%d+K%d+I%d+G%d)",7 + i,7 + i,7 + i,7 + i,7 + i));
//
//            cell = sheet.getCellRange(7 + i, 45);
//            cell.setStyle(style2);
//            cell.setFormula( String.format("=SUM(T%d+V%d+X%d+AB%d+AF%d+AK%d+AR%d)",7 + i,7 + i,7 + i,7 + i,7 + i,7 + i,7 + i));
//
//            cell = sheet.getCellRange(7 + i, 51);
//            cell.setStyle(style2);
//            cell.setFormula( String.format("=SUM(R%d+AS%d+AX%d)",7 + i,7 + i,7 + i));
//
//        }
//
//        //获取单元格，设置内边框、外边框
//        CellRange range1 = sheet.getCellRange("A1:AZ500");
//        range1.borderInside(LineStyleType.Thin, Color.BLACK);
//        range1.borderAround(LineStyleType.Medium, Color.BLACK);
//
//
//        //保存文档
//        workbook.saveToFile("output.xlsx", ExcelVersion.Version2013);
//        try {
//            // 设置输出的格式
//            response.setContentType("application/octet-stream");
//            response.setCharacterEncoding("UTF-8");
//            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
//            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("fileName","utf-8"));
//            workbook.saveToStream(response.getOutputStream(), FileFormat.Version2013);
//        } catch (IOException  e) {
//            e.printStackTrace();
//        }
//    }
//
//
//    public static void outputScore(List<Student> students, List<Examdetail> examdetailList, List<Score> scoreList, HttpServletResponse response) {
//        //加载Excel文档
//        Workbook workbook = new Workbook();
//
//        //创建数据单元格样式
//        CellStyle style2 = workbook.getStyles().addStyle("Data Style");
//        style2.getFont().setSize(10f);
//        style2.getFont().setColor(Color.BLACK);
//
//
//        ClassPathResource classPathResource = new ClassPathResource("/ftl/nineScoreExcelTemplate.xlsx");
//        InputStream inputStream = null;
//        try {
//            inputStream = classPathResource.getInputStream();
//        } catch (IOException e) {
//            e.printStackTrace();
//        }
//
//        workbook.loadFromStream(inputStream, ExcelVersion.Version2013);
//
//        //获取第2张工作表
//        Worksheet sheet = workbook.getWorksheets().get(0);
//
//        Map<Integer, List<Examdetail>> examdetailListMap = examdetailList.stream().collect(Collectors.groupingBy(Examdetail::getStuId));
//        Map<Integer, List<Score>> scoreListMap = scoreList.stream().collect(Collectors.groupingBy(Score::getExamdetailId));
//
//        for (int i = 0; i < students.size(); i++) {
//            Student student = students.get(i);
//            //班级
//            CellRange cell = sheet.getCellRange(7 + i, 1);
//            cell.setValue(student.getClassName());
//            cell.setStyle(style2);
//            //身份证
//            cell = sheet.getCellRange(7 + i, 2);
//            cell.setValue("'" + student.getCardId());
//            cell.setStyle(style2);
//            //学籍号
//            cell = sheet.getCellRange(7 + i, 3);
//            cell.setValue("'" + student.getStuNo());
//            cell.setStyle(style2);
//
//            cell = sheet.getCellRange(7 + i, 4);
//            cell.setValue(student.getName());
//            cell.setStyle(style2);
//
//            cell = sheet.getCellRange(7 + i, 5);
//            cell.setValue(student.getSex() == 1 ? "男" : "女");
//            cell.setStyle(style2);
//
//
//            List<Examdetail> examdetails = examdetailListMap.get(student.getId());
//            int upXkCount = 1;
//            int dowmXkCount = 1;
//
//            Examdetail sgExamdetail = null;
//            Examdetail tzExamdetail = null;
//            Examdetail fhlExamdetail = null;
//
//            for (int j = 0; j < examdetails.size(); j++) {
//                Examdetail examdetail = examdetails.get(j);
//                List<Score> scoreList2 = scoreListMap.get(examdetail.getId());
//                if (scoreList2 == null) continue;
//                TransScore transScore = getScore(examdetail, scoreList2);
//                if (examdetail.getSemester() == 5 && examdetail.getSubId() == 1) {
//                    cell = sheet.getCellRange(7 + i, 6);
//                    cell.setValue("'" + transScore.getRawScore() + "s");
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 7);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 5 && examdetail.getSubId() == 2) {
//                    cell = sheet.getCellRange(7 + i, 8);
//                    cell.setValue(transScore.getRawScore() + "个");
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 9);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 5 && examdetail.getSubId() == 3) {
//                    cell = sheet.getCellRange(7 + i, 10);
//                    cell.setValue("'" + transScore.getRawScore());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 11);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 5 && examdetail.getSubId() == 4) {
//                    cell = sheet.getCellRange(7 + i, 10);
//                    cell.setValue("'" + transScore.getRawScore());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 11);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                //6选2
//                if (examdetail.getSemester() == 5 && (examdetail.getSubId() >= 5 && examdetail.getSubId() <= 11)) {
//                    if (upXkCount == 1) {
//                        cell = sheet.getCellRange(7 + i, 12);
//                        cell.setValue(SubjectEnum.getNameById(examdetail.getSubId()));
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(7 + i, 13);
//                        cell.setValue(transScore.getRawScore());
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(7 + i, 14);
//                        cell.setValue(transScore.getConvScore());
//                        cell.setStyle(style2);
//                        upXkCount++;
//                    }
//
//                    if (upXkCount == 2) {
//                        cell = sheet.getCellRange(7 + i, 15);
//                        cell.setValue(SubjectEnum.getNameById(examdetail.getSubId()));
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(7 + i, 16);
//                        cell.setValue(transScore.getRawScore());
//                        cell.setStyle(style2);
//
//                        cell = sheet.getCellRange(7 + i, 17);
//                        cell.setValue(transScore.getConvScore());
//                        cell.setStyle(style2);
//                    }
//                }
//
//
//                if (examdetail.getSemester() == 6 && examdetail.getSubId() == 12) {
//                    cell = sheet.getCellRange(7 + i, 19);
//                    cell.setValue("'" + transScore.getRawScore() + "s");
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 20);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 6 && examdetail.getSubId() == 2) {
//                    cell = sheet.getCellRange(7 + i, 21);
//                    cell.setValue("'" + transScore.getRawScore() + "个");
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 22);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 6 && examdetail.getSubId() == 3) {
//                    cell = sheet.getCellRange(7 + i, 23);
//                    cell.setValue("'" + transScore.getRawScore());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 24);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSemester() == 6 && examdetail.getSubId() == 4) {
//                    cell = sheet.getCellRange(7 + i, 23);
//                    cell.setValue("'" + transScore.getRawScore());
//                    cell.setStyle(style2);
//
//                    cell = sheet.getCellRange(7 + i, 24);
//                    cell.setValue(transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSubId() == 26) {
//                    sgExamdetail = examdetail;
//                }
//                if (examdetail.getSubId() == 13) {
//                    tzExamdetail = examdetail;
//                }
//                if (examdetail.getSubId() == 14) {
//                    fhlExamdetail = examdetail;
//                }
//
//                if (examdetail.getSubId() == 15 || examdetail.getSubId() == 16 || examdetail.getSubId() == 17) {
//                    SubjectEnum subject = SubjectEnum.getEnumBySubId(examdetail.getSubId());
//                    cell = sheet.getCellRange(7 + i, 33);
//                    cell.setValue("'" + subject.getMessage());
//                    cell.setStyle(style2);
//                    if (transScore.getTransScoreItemList() == null) {
//                        continue;
//                    }
//
//                    for (int k = 0; k < transScore.getTransScoreItemList().size(); k++) {
//                        TransScore.TransScoreItem transScoreItem = transScore.getTransScoreItemList().get(k);
//                        cell = sheet.getCellRange(7 + i, 34 + k);
//                        if (examdetail.getSubId() == 17 && k == 0) {
//                            cell.setValue("行进间正面双手连续传垫球 " + transScoreItem.getRawScore() + " - " + transScoreItem.getConvScore());
//                        }
//                        if (examdetail.getSubId() == 17 && k == 1) {
//                            cell.setValue("正面上手发球（男）/正面下手发球（女）" + transScoreItem.getRawScore() + "-" + transScoreItem.getConvScore());
//                        }
//                        cell.setStyle(style2);
//                    }
//
//                    cell = sheet.getCellRange(7 + i, 37);
//                    cell.setValue("'" + transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//
//                if (examdetail.getSubId() >= 18 && examdetail.getSubId() <= 22) {
//                    SubjectEnum subject = SubjectEnum.getEnumBySubId(examdetail.getSubId());
//                    cell = sheet.getCellRange(7 + i, 38);
//                    cell.setValue("'" + subject.getMessage());
//                    cell.setStyle(style2);
//
//                    for (int k = 0; k < transScore.getTransScoreItemList().size(); k++) {
//                        TransScore.TransScoreItem transScoreItem = transScore.getTransScoreItemList().get(k);
//                        cell = sheet.getCellRange(7 + i, 39 + k);
//                        if (examdetail.getSubId() == 22 && k == 0) {
//                            cell.setValue("10秒钟俯卧撑+左、右直拳+深蹲+左、右鞭腿 - " + transScoreItem.getRawScore() + " -组 " + transScoreItem.getConvScore());
//                        }
//                        if (examdetail.getSubId() == 22 && k == 1) {
//                            cell.setValue("10秒钟左、右正蹬腿 - " + transScoreItem.getRawScore() + " -组 " + transScoreItem.getConvScore());
//                        }
//                        cell.setStyle(style2);
//                    }
//
//                    cell = sheet.getCellRange(7 + i, 44);
//                    cell.setValue("'" + transScore.getConvScore());
//                    cell.setStyle(style2);
//                }
//            }
//            if (sgExamdetail != null && tzExamdetail != null) {
//                List<Score> scoreList2 = scoreListMap.get(sgExamdetail.getId());
//                TransScore sgTransScore = getScore(sgExamdetail, scoreList2);
//
//                List<Score> scoreList3 = scoreListMap.get(tzExamdetail.getId());
//                TransScore tzTransScore = getScore(tzExamdetail, scoreList3);
//
//                cell = sheet.getCellRange(7 + i, 25);
//                cell.setValue(sgTransScore.getRawScore());
//                cell.setStyle(style2);
//
//                cell = sheet.getCellRange(7 + i, 26);
//                cell.setValue(tzTransScore.getRawScore());
//                cell.setStyle(style2);
//
//                Double bmi = Double.parseDouble(tzTransScore.getRawScore()) / ((Double.parseDouble(sgTransScore.getRawScore()) / 100.0) * (Double.parseDouble(sgTransScore.getRawScore()) / 100.0));
//                double convScore = 0;
//                if ((student.getSex() == 1 && bmi <= 26) || (student.getSex() == 2 && bmi <= 25.1)) {
//                    convScore = 1.5;
//                }
//
//                cell = sheet.getCellRange(7 + i, 27);
//                cell.setValue("'" + df.format(bmi));
//                cell.setStyle(style2);
//
//                cell = sheet.getCellRange(7 + i, 28);
//                cell.setValue(convScore + "");
//                cell.setStyle(style2);
//
//            }
//
//            if (fhlExamdetail != null && tzExamdetail != null) {
//                List<Score> scoreList2 = scoreListMap.get(fhlExamdetail.getId());
//                TransScore fhlTransScore = getScore(fhlExamdetail, scoreList2);
//
//                List<Score> scoreList3 = scoreListMap.get(tzExamdetail.getId());
//                TransScore tzTransScore = getScore(tzExamdetail, scoreList3);
//
//                cell = sheet.getCellRange(7 + i, 29);
//                cell.setValue(fhlTransScore.getRawScore());
//                cell.setStyle(style2);
//
//                cell = sheet.getCellRange(7 + i, 30);
//                cell.setValue(tzTransScore.getRawScore());
//                cell.setStyle(style2);
//
//
//                cell = sheet.getCellRange(7 + i, 31);
//                cell.setValue("'" + df.format(Double.parseDouble(fhlTransScore.getRawScore()) / Double.parseDouble(tzTransScore.getRawScore())));
//
//                cell.setStyle(style2);
//
//                double convScore = 0;
//                if ((student.getSex() == 1 && Double.parseDouble(fhlTransScore.getRawScore()) / Double.parseDouble(tzTransScore.getRawScore()) <= 50) || (student.getSex() == 2 && Double.parseDouble(fhlTransScore.getRawScore()) / Double.parseDouble(tzTransScore.getRawScore()) <= 60)) {
//                    convScore = 1.5;
//                }
//
//                cell = sheet.getCellRange(7 + i, 32);
//                cell.setValue(convScore + "");
//                cell.setStyle(style2);
//
//            }
//
//            cell = sheet.getCellRange(7 + i, 18);
//            cell.setFormula("=SUM(Q7+N7+K7+I7+G7)");
//            cell.setStyle(style2);
//        }
//
//        //获取单元格，设置内边框、外边框
//        CellRange range1 = sheet.getCellRange("A1:AZ500");
//        range1.borderInside(LineStyleType.Thin, Color.BLACK);
//        range1.borderAround(LineStyleType.Medium, Color.BLACK);
//
//
//        //保存文档
//        workbook.saveToFile("output.xlsx", ExcelVersion.Version2013);
//        try {
//            // 设置输出的格式
//            response.setContentType("application/octet-stream");
//            response.setCharacterEncoding("UTF-8");
//            response.setHeader("Access-Control-Expose-Headers","Content-Disposition");
//            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("fileName","utf-8"));
//            workbook.saveToStream(response.getOutputStream(), FileFormat.Version2013);
//        } catch (IOException  e) {
//            e.printStackTrace();
//        }
//    }
//
//    static NumberFormat nf = NumberFormat.getNumberInstance();
//    static {
//        //保留两位小数
//        nf.setMaximumFractionDigits(2);
//        //如果不需要四舍五入可以使用RoundingMode.DOWN
//        nf.setRoundingMode(RoundingMode.UP);
//    }
//
////    public static TransScore getScore(Examdetail examdetail, List<Score> scoreList) {
////
////        if (SubjectEnum.isHaveSubItems(examdetail.getSubId())) {
////            TransScore transScore = new TransScore();
////            List<TransScore.TransScoreItem> transScoreItemList = new ArrayList<>();
////            Map<Integer, List<Score>> map = scoreList.stream().collect(Collectors.groupingBy(Score::getItemId));
////            map.forEach((key, value) -> {
////                AtomicReference<String> highest = new AtomicReference<String>("");
////                AtomicReference<Double> highestConv = new AtomicReference<>(0.0);
////                List<Score> scoreList2 = value;
////                AtomicBoolean isTrue = new AtomicBoolean(true);
////                scoreList2.stream().max(Comparator.comparingDouble(e -> {
////                    if (e.getConversionScore() != null) {
////                        return Double.parseDouble(e.getConversionScore());
////                    }
////                    return 0;
////                })).ifPresent(e -> {
////                    if (e.getItemId() != null && e.getItemId() != 0 && e.getItemId() != -0) {
////                        highest.set(e.getRawScore());
////                        ;
////                        if (e.getConversionScore() != null && !e.getConversionScore().trim().equals("")) {
////                            highestConv.set(Double.parseDouble(e.getConversionScore()));
////                        }
////                    } else {
////                        isTrue.set(false);
////                    }
////
////                });
////                if (isTrue.get()) {
////                    TransScore.TransScoreItem transScoreItem = new TransScore.TransScoreItem();
////                    transScoreItem.setRawScore(highest.get());
////                    transScoreItem.setConvScore(nf.format(highestConv.get()));
////                    transScoreItemList.add(transScoreItem);
////                }
////
////            });
////            double totalScore = 0;
////            for (int i = 0; i < transScoreItemList.size(); i++) {
////                totalScore += Double.parseDouble(transScoreItemList.get(i).getConvScore());
////            }
////            transScore.setConvScore(nf.format(totalScore));
////            transScore.setTransScoreItemList(transScoreItemList);
////            return transScore;
////        } else {
////            TransScore transScore = new TransScore();
////
////            AtomicReference<String> highest = new AtomicReference<String>("");
////            AtomicReference<Double> highestConv = new AtomicReference<>(0.0);
////            scoreList.stream().max(Comparator.comparingDouble(e -> {
////                if (e.getConversionScore() != null) {
////                    return Double.parseDouble(e.getConversionScore());
////                }
////                return 0;
////            })).ifPresent(e -> {
////                highest.set(e.getRawScore());
////                ;
////                if (e.getConversionScore() != null && !e.getConversionScore().trim().equals("")) {
////                    highestConv.set(Double.parseDouble(e.getConversionScore()));
////                }
////            });
////
////            transScore.setRawScore(highest.get());
////            transScore.setConvScore(String.valueOf(highestConv.get()));
////            return transScore;
////        }
////    }
////
//
//    @Data
//    @NoArgsConstructor
//    @AllArgsConstructor
//    private static class SgandTzScore {
//        private String className;
//
//        private String cardId;
//
//        private String StuNo;
//
//        private String name;
//
//        private Double bmi;
//
//        private Double fhlZs;
//
//
//    }
//}
